Precision Performance Surgery for PostgreSQL: LLVM-based Expression Compilation, Just in Time

We demonstrate how the compilation of SQL expressions into machine code leads to significant query runtime improvements in PostgreSQL 9. Our primary goal is to connect recent research in query code generation with one of the most widely deployed database engines. The approach calls on LLVM to translate arithmetic and filter expressions into native x86 instructions just before SQL query execution begins. We deliberately follow a non-invasive design that does not turn PostgreSQL on its head: interpreted and compiled expression evaluation coexist and both are used to execute the same query. We will bring an enhanced version of PostgreSQL that exhibits notable runtime savings and provides visual insight into exactly where and how execution plans can benefit from SQL expression compilation. 1. WHAT TOOK YOU SO LONG, POSTGRESQL? In a discussion of query processing strategies, the evaluation of SQL expressions—here we refer to expressions over scalar values, notably of number types as well as Booleans— typically assumes a second-tier role. Still, expression evaluation is pervasive in query plan execution: table scans, filters, aggregates, projections, and even joins (those which do not enjoy index support) inherently rely on it. Indeed, in the case of TPC-H [7], the inefficient evaluation of complex expressions has been identified as a major choke point [2, see choke point CP 4.1d “interpreter overhead”]. The premise of the present work is that significant query runtime improvements are obtained if we can speed up expression evaluation. Expression Evaluation in the Limelight. Figure 1 shows query Q1 of the TPC-H benchmark with a particular focus on the SQL expressions that are embedded in this query: • a Boolean filter expression 1 that compares values of type date (the date difference operator is evaluated at query compile time and thus is of no concern in the context of this work) and This work is licensed under the Creative Commons AttributionNonCommercial-NoDerivatives 4.0 International License. To view a copy of this license, visit http://creativecommons.org/licenses/by-nc-nd/4.0/. For any use beyond those covered by this license, obtain permission by emailing [email protected]. Proceedings of the VLDB Endowment, Vol. 9, No. 13 Copyright 2016 VLDB Endowment 2150-8097/16/09. 1 2 1 SELECT l_returnflag, l_linestatus, 2 SUM(l_quantity) AS sum_qty, 3 SUM(l_extendedprice) AS sum_base_price, 4 SUM(l_extendedprice*(1-l_discount)) AS sum_disc_price, 5 SUM(l_extendedprice*(1-l_discount)*(1+l_tax)) AS sum_charge, 6 AVG(l_quantity) AS avg_qty, 7 AVG(l_extendedprice) AS avg_price, 8 AVG(l_discount) AS avg_disc, 9 COUNT(*) AS count_order 10 FROM lineitem 11 WHERE l_shipdate <= date ’1998-12-01’ interval ’103 days’ 12 GROUP BY l_returnflag, l_linestatus 13 ORDER BY l_returnflag, l_linestatus; Figure 1: TPC-H Q1. We focus on the evaluation of SQL expressions (here: 1 and 2 ) embedded in such queries. • a group 2 of aggregates whose arguments are arithmetic expressions over double precision columns and literals. The execution of query Q1 involves a substantial expression evaluation effort. Table 1 displays an excerpt of a function call profile, recorded while PostgreSQL 9 was executing Q1 over a TPC-H instance of scale factor 5. We see that the scan of table lineitem leads to 29 999 799 invocations of function ExecQual to evaluate filter expression 1 over the incoming rows (about 520 000 of those do not qualify such that ExecScan returns 29 477 776 times to its caller, each time delivering an individual qualifying row). Each such row leads to the evaluation of the 8 aggregates and their arithmetic expressions 2 , yielding a total of 8 × 29 477 776 ≈ 235 582 212 invocations of ExecProject, the PostgreSQL function that evaluates expressions in a query’s SELECT clause. Finally, caller ExecAgg returns 4 times to deliver the rows of aggregates computed by Q1. This expression evaluation workload has a measurable impact on query runtime. Indeed, in the case of Q1, PostgreSQL spends the lion share of the execution time on expression evaluation. The pie charts of Figure 2 detail this impact for an entire set of TPC-H queries (we have selected these queries because they embed several and/or complex SQL expressions—Q19, for example, contains a variety of filters, see Figure 5 below). Here, the darker pie slices account for the overall execution time spent in all functions in the call tree below ExecQual and ExecProject. During the execution of Q1, PostgreSQL is busy with expressions about 12.1% + 39.8% = 51.9% of the time—for the further queries 1 ExecScan and ExecAgg return one additional time only to indicate that no more rows will be delivered. PostgreSQL implements a Volcano-style iterator model [3].